﻿Imports DTO
Imports System.Data.OleDb

Public Class DocGiaDAO

    Public Function LayDocGia() As List(Of DocGiaDTO)

        Dim sql = "select * from DocGia "
        Dim con As OleDbConnection = DatabaseProvider.connectData() 'ket noi csdl
        Dim cmd As New OleDbCommand(sql, con) ' thuc hien ket noi
        Dim reader As OleDbDataReader = cmd.ExecuteReader() ' thuc thi 
        Dim danhsach As New List(Of DocGiaDTO) 'khai báo danhsach de du lieu do vao nó
        While reader.Read()
            Dim nvDTO As New DocGiaDTO() ' khai báo doi tuong nvDTO de gán nhung doi tuong trong csdl vao
            nvDTO.MaDG = reader("MaDocGia") 'gán manv vao
            nvDTO.HoTen = reader("HoTenDG")
            nvDTO.DiaChi = reader("DiaChi")
            nvDTO.MaLoaiDocGia = reader("MaLoaiDocGia")
            nvDTO.Email = reader("Email")
            nvDTO.NgaySinh = reader("NgaySinh")
            nvDTO.NgayLapThe = reader("NgayLapThe")
            nvDTO.NgayHetHan = reader("NgayHetHan")
            nvDTO.MaNV = reader("MaNV")
            nvDTO.SoSachDangMuon = reader("SoSachDangMuon")
            nvDTO.TinhTrangThe = reader("TinhTrangThe")

            danhsach.Add(nvDTO) ' add doi tuong nvDTO vao danhsach
        End While

        con.Close() ' đóng kết nối
        Return danhsach
    End Function


    Public Shared Function TimKiem(ByVal maDocGia As Integer)
        Dim dgDto As New DocGiaDTO
        Dim cn As OleDbConnection
        'B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
        cn = DatabaseProvider.connectData()
        'B3:Tao chuoi  strSQL thao tac co so du lieu
        Dim strSQL As String
        strSQL = "Select * From DocGia where Ma = ?"
        'B4:Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@Ma", OleDbType.Integer)
        cmd.Parameters("@Ma").Value = maDocGia
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            dgDto.MaDG = dr("MaDG")
            dgDto.HoTen = dr("HoTen")
            dgDto.DiaChi = dr("DiaChi")
            dgDto.Email = dr("Email")
            dgDto.NgayLapThe = dr("NgayLapThe")
            dgDto.NgaySinh = dr("NgaySinh")
            dgDto.MaLoaiDocGia = dr("MaLoaiDocGia")


        End While
        'B5:Dong ket noi CSDL
        dr.Close()
        cn.Close()
        Return dgDto

    End Function
    Public Function TimKiem(ByVal dgCrt As DocGiaCrt) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DatabaseProvider.connectData()
        Dim cmd As OleDbCommand = BuildQuery(dgCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt



    End Function

    Private Function BuildQuery(ByVal dgCrt As DocGiaCrt, ByVal cn As OleDbConnection)

        Dim cmd As New OleDbCommand()
        Dim strDKMaDocGia = " 1=1 "
        Dim strDKHoTen = " 1=1 "
        Dim strDKDiaChi = " 1=1 "
        Dim strDKEmail = " 1=1 "
        Dim strDKNgaySinh = " 1=1 "
        Dim strDKNgayLapThe = " 1=1 "
        Dim strDKLoaiDocGia = " 1=1 "

        If (dgCrt.MaDocGia <> 0) Then
            strDKMaDocGia = " MaDocGia = ? "
            cmd.Parameters.Add("@Ma", OleDbType.Integer)
            cmd.Parameters("@Ma").Value = dgCrt.MaDocGia

        End If

        If (dgCrt.HoTen <> "") Then
            strDKHoTen = "HoTen like ?"
            cmd.Parameters.Add("@Ten", OleDbType.WChar)
            cmd.Parameters("@Ten").Value = "%" + dgCrt.HoTen + "%"

        End If

        If (dgCrt.CheckDiaChi And dgCrt.DiaChi <> "") Then

            strDKDiaChi = "DiaChi like ?"
            cmd.Parameters.Add("@DiaChi", OleDbType.WChar)
            cmd.Parameters("@DiaChi").Value = "%" + dgCrt.DiaChi + "%"

        End If

        If (dgCrt.CheckEmail) Then
            strDKEmail = "Email like ?"
            cmd.Parameters.Add("@Email", OleDbType.WChar)
            cmd.Parameters("@Email").Value = "%" + dgCrt.Email + "%"
        End If

        If (dgCrt.CheckNgaySinh) Then
            strDKNgaySinh = "NgaySinh between ? and ? "
            cmd.Parameters.Add("@NgaySinhTu", OleDbType.Date)
            cmd.Parameters.Add("@NgaySinhDen", OleDbType.Date)
            cmd.Parameters("@NgaySinhTu").Value = dgCrt.NgaySinhTu
            cmd.Parameters("@NgaySinhDen").Value = dgCrt.NgaySinhDen
        End If

        If (dgCrt.CheckNgayLapThe) Then
            strDKNgayLapThe = " NgayLapThe = ? "
            cmd.Parameters.Add("@NgayLapThe", OleDbType.Date)
            cmd.Parameters("@NgayLapThe").Value = dgCrt.NgayLapThe
        End If

        If (dgCrt.CheckMaLoaiDocGia) Then
            strDKLoaiDocGia = "MaLoaiDocGia = ? "
            cmd.Parameters.Add("@MaLoaiDocGia", OleDbType.Integer)
            cmd.Parameters("@MaLoaiDocGia").Value = dgCrt.MaLoaiDocGia
        End If

        Dim strDKWhere As String = "Where"
        strDKWhere += strDKMaDocGia
        strDKWhere += " and " + strDKHoTen
        strDKWhere += " and " + strDKDiaChi
        strDKWhere += " and " + strDKEmail
        strDKWhere += " and " + strDKNgaySinh
        strDKWhere += " and " + strDKNgayLapThe
        strDKWhere += " and " + strDKLoaiDocGia

        Dim strSQL As String = "Select * From DocGia "
        strSQL += strDKWhere
        strSQL += "Order by HoTen"

        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd
    End Function

End Class
